package com.infore.statisticsAndAlarm.repository;

import java.util.Date;
import java.util.List;
import java.util.Map;

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import com.infore.statisticsAndAlarm.entity.MonthData;

@Repository
public interface IMonthDataRepository extends JpaRepository<MonthData, String>, JpaSpecificationExecutor<MonthData> {

	List<MonthData> findByStationCodeInAndFactorCodeInAndDataTimeBetweenOrderByDataTimeAsc(List<String> stationCode,
			List<String> factorCode, Date start, Date end);

	List<MonthData> findByStationCodeInAndDataTimeBetweenOrderByDataTimeAsc(List<String> stationCode, Date start,
			Date end);

	// 通过站点，因子，时间段，得到每一条记录,返回listMap---用于单点自动站报表
	@Query(value = "select t.station_code as MN,t.factor_code as CODE,t.data_time as TIME,t.data_value_avg as VALUE"
			+ " from MONTH_DATA t where (t.station_code in (:mnList))"
			+ " and (t.factor_code in (:projectCodes)) and (t.data_time between :startDate and :endDate)"
			+ " order by t.data_time asc", nativeQuery = true)
	List<Map<String, Object>> audioSingReport(@Param("startDate") Date startDate, @Param("endDate") Date endDate,
			@Param("projectCodes") List<String> projectCodes, @Param("mnList") List<String> mnList);

	// 通过站点，因子，时间段，得到每一条记录,返回listMap---用于多点自动站报表
	@Query(value = "select t.station_code as MN, t.factor_code as CODE , to_char( avg(t.data_value_avg),'fm999999990.999999999') as VALUE ,to_char(sum(t.real_num)) as COUNT "
			+ "from MONTH_DATA t where (t.station_code in (:mnList)) and (t.factor_code in (:projectCodes)) and (t.data_time between :startDate and :endDate) group by t.station_code,t.factor_code order by t.station_code", nativeQuery = true)
	List<Map<String, Object>> audioMoreReport(@Param("startDate") Date startDate, @Param("endDate") Date endDate,
			@Param("projectCodes") List<String> projectCodes, @Param("mnList") List<String> mnList);
}
